﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using System.Web;


using SIPO.Library;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Transaction
{
    public partial class recAcceptanceDetailRepo
    {
        public List<recAcceptanceDetail> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameter)
        {
            List<recAcceptanceDetail> tt = new List<recAcceptanceDetail>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      AcceptanceDetailId, AcceptanceId, SectorCode, SubSectorCode, SubSubSectorCode, Level, KeyId, Description, [Values], CreatedDate, CreatedBy, UpdatedDate, UpdatedBy,IsLeaf,IsExpand ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recAcceptanceDetail ");
                    sbQuery.AppendFormat("      {0} ", parameter);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recAcceptanceDetail t = new recAcceptanceDetail();
                                t.AcceptanceDetailId = dr[recAcceptanceDetail.Fields.AcceptanceDetailId] == DBNull.Value ? 0 : int.Parse(dr[recAcceptanceDetail.Fields.AcceptanceDetailId].ToString());
                                t.AcceptanceId = dr[recAcceptanceDetail.Fields.AcceptanceId] == DBNull.Value ? 0 : int.Parse(dr[recAcceptanceDetail.Fields.AcceptanceId].ToString());
                                t.SectorCode = dr[recAcceptanceDetail.Fields.SectorCode] == DBNull.Value ? (int?)null : int.Parse(dr[recAcceptanceDetail.Fields.SectorCode].ToString());
                                t.SubSectorCode = dr[recAcceptanceDetail.Fields.SubSectorCode] == DBNull.Value ? (int?)null : int.Parse(dr[recAcceptanceDetail.Fields.SubSectorCode].ToString());
                                t.SubSubSectorCode = dr[recAcceptanceDetail.Fields.SubSubSectorCode] == DBNull.Value ? (int?)null : int.Parse(dr[recAcceptanceDetail.Fields.SubSubSectorCode].ToString());
                                t.Level = dr[recAcceptanceDetail.Fields.Level] == DBNull.Value ? (int?)null : int.Parse(dr[recAcceptanceDetail.Fields.Level].ToString());
                                t.KeyId = dr[recAcceptanceDetail.Fields.KeyId] == DBNull.Value ? (int?)null : int.Parse(dr[recAcceptanceDetail.Fields.KeyId].ToString());
                                t.Description = dr[recAcceptanceDetail.Fields.Description] == DBNull.Value ? string.Empty : dr[recAcceptanceDetail.Fields.Description].ToString();
                                t.Values = dr[recAcceptanceDetail.Fields.Values] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recAcceptanceDetail.Fields.Values].ToString());
                                t.CreatedDate = dr[recAcceptanceDetail.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recAcceptanceDetail.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recAcceptanceDetail.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recAcceptanceDetail.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recAcceptanceDetail.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recAcceptanceDetail.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recAcceptanceDetail.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recAcceptanceDetail.Fields.UpdatedBy].ToString();
                                t.IsLeaf = dr[recAcceptanceDetail.Fields.IsLeaf] == DBNull.Value ? false : bool.Parse(dr[recAcceptanceDetail.Fields.IsLeaf].ToString());
                                t.IsExpand = dr[recAcceptanceDetail.Fields.IsExpand] == DBNull.Value ? false : bool.Parse(dr[recAcceptanceDetail.Fields.IsExpand].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameter)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM transactions.recAcceptanceDetail {1}", column, parameter);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM transactions.recAcceptanceDetail {1}", column, parameter);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM transactions.recAcceptanceDetail {1}", column, parameter);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM transactions.recAcceptanceDetail {1}", column, parameter);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM transactions.recAcceptanceDetail {1}", column, parameter);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM transactions.recAcceptanceDetail {1}", column, parameter);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public List<recAcceptanceDetail> GetListHierarchy()
        {
            List<recAcceptanceDetail> tt = new List<recAcceptanceDetail>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                    WITH result_set AS 
                                    (
	                                    select null as ParentID, CAST(SectorID AS VARCHAR(10)) As ID, SectorCode as [KeyCode], SectorName as [Description], 0 AS [Values], 1 as [Level] from master.recSector
	                                    union all
	                                    select CAST(recSector.SectorID AS Varchar(2)),  CAST(CAST(recSector.SectorID AS Varchar(2)) + '.' + CAST(SubSectorID AS Varchar(3)) AS Varchar(10)) AS KeyID, SubSectorCode, SubSectorName, 0 as [values] , 2 as [Level] from master.recSubSector
	                                    inner join  master.recSector on (recSector.SectorID = recSubSector.SectorID)
	                                    union all
	                                    select CAST(recSubSector.SectorID AS Varchar(2)) + '.' + CAST(recSubSector.SubSectorID AS Varchar(3)), CAST(CAST(recSubSector.SectorID AS Varchar(2)) + '.' + CAST(recSubSector.SubSectorID AS Varchar(3)) 
	                                    + '.' + CAST(recSubSubSector.SubSubSectorID AS Varchar(3)) AS Varchar(10)) AS KeyID,
	                                    SubSubSectorCode, SubSubSectorName, 0 as [Values], 3 as [Level] from master.recSubSubSector
	                                    inner join  master.recSubSector on (recSubSector.SubSectorID = recSubSubSector.SubSectorID)
                                    )
                                    select 
	                                    ParentID,
	                                    ID, 
	                                    KeyCode,
	                                    CASE
		                                    WHEN [Level]= 2 THEN SPACE(3)+ [Description]
		                                    WHEN [Level]= 3 THEN SPACE(5)+ [Description]
	                                    ELSE [Description]
	                                    END [Description],
	                                    [Values],
                                        [Level],
                                        CASE
											WHEN [Level] = 1 THEN CAST(0 as bit)
											WHEN [Level] = 2 THEN CAST(0 as bit)
											Else											
												CAST(1 as bit) 
                                        END as IsLeaf,
                                        CASE
											WHEN [Level] = 1 THEN CAST(1 as bit)
											WHEN [Level] = 2 THEN CAST(1 as bit)
											Else											
												CAST(0 as bit) 
                                        END as IsExpand
                                    from result_set order by ID
                                    ";
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recAcceptanceDetail t = new recAcceptanceDetail();

                                t.ParentID = dr[recAcceptanceDetail.Fields.ParentID] == DBNull.Value ? string.Empty : dr[recAcceptanceDetail.Fields.ParentID].ToString();

                                t.KeyCode = dr[recAcceptanceDetail.Fields.KeyCode] == DBNull.Value ? string.Empty : dr[recAcceptanceDetail.Fields.KeyCode].ToString();

                                t.Level = dr[recAcceptanceDetail.Fields.Level] == DBNull.Value ? (int?)null : int.Parse(dr[recAcceptanceDetail.Fields.Level].ToString());
                                t.ID = dr[recAcceptanceDetail.Fields.ID] == DBNull.Value ? string.Empty : dr[recAcceptanceDetail.Fields.ID].ToString();
                                t.Description = dr[recAcceptanceDetail.Fields.Description] == DBNull.Value ? string.Empty : dr[recAcceptanceDetail.Fields.Description].ToString();
                                t.Values = dr[recAcceptanceDetail.Fields.Values] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recAcceptanceDetail.Fields.Values].ToString());

                                t.IsLeaf = dr[recAcceptanceDetail.Fields.IsLeaf] == DBNull.Value ? false : bool.Parse(dr[recAcceptanceDetail.Fields.IsLeaf].ToString());
                                t.IsExpand = dr[recAcceptanceDetail.Fields.IsExpand] == DBNull.Value ? false : bool.Parse(dr[recAcceptanceDetail.Fields.IsExpand].ToString());

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recAcceptanceDetail> GetList(string parameters)
        {
            List<recAcceptanceDetail> tt = new List<recAcceptanceDetail>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                                        SELECT
                                        CASE
	                                        WHEN [Level] = 1 THEN Null 
	                                        WHEN [Level] = 2 THEN CAST(SectorCode AS VARCHAR(1)) 
	                                        WHEN [Level] = 3 THEN CAST(SectorCode AS VARCHAR(1)) + '.' + CAST(SubSectorCode as VARCHAR(2)) 
	                                        ELSE NULL
                                        END ParentID,
                                        CASE
	                                        WHEN [Level] = 1 THEN CAST(SectorCode AS VARCHAR(1)) 
	                                        WHEN [Level] = 2 THEN CAST(SectorCode AS VARCHAR(1)) + '.' + CAST(SubSectorCode as VARCHAR(2))
	                                        WHEN [Level] = 3 THEN CAST(SectorCode AS VARCHAR(1)) + '.' + CAST(SubSectorCode as VARCHAR(2)) + '.' + CAST(SubSubSectorCode as VARCHAR(3)) 
	                                        ELSE NULL
                                        END ID,
                                        CASE
	                                        WHEN [Level] = 1 THEN CAST(SectorCode AS VARCHAR(1)) 
	                                        WHEN [Level] = 2 THEN CAST(SubSectorCode AS VARCHAR(2)) 
	                                        WHEN [Level] = 3 THEN CAST(SubSubSectorCode AS VARCHAR(3)) 
	                                        ELSE NULL
                                        END KeyCode,
                                        [Description],
                                        [Values], [Level], IsLeaf, IsExpand 
                                        from transactions.recAcceptanceDetail " + parameters;
                    cmd.CommandType = CommandType.Text;

                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recAcceptanceDetail t = new recAcceptanceDetail();

                                t.ParentID = dr[recAcceptanceDetail.Fields.ParentID] == DBNull.Value ? string.Empty : dr[recAcceptanceDetail.Fields.ParentID].ToString();

                                t.KeyCode = dr[recAcceptanceDetail.Fields.KeyCode] == DBNull.Value ? string.Empty : dr[recAcceptanceDetail.Fields.KeyCode].ToString();

                                t.Level = dr[recAcceptanceDetail.Fields.Level] == DBNull.Value ? (int?)null : int.Parse(dr[recAcceptanceDetail.Fields.Level].ToString());
                                t.ID = dr[recAcceptanceDetail.Fields.ID] == DBNull.Value ? string.Empty : dr[recAcceptanceDetail.Fields.ID].ToString();
                                t.Description = dr[recAcceptanceDetail.Fields.Description] == DBNull.Value ? string.Empty : dr[recAcceptanceDetail.Fields.Description].ToString();
                                t.Values = dr[recAcceptanceDetail.Fields.Values] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recAcceptanceDetail.Fields.Values].ToString());

                                t.IsLeaf = dr[recAcceptanceDetail.Fields.IsLeaf] == DBNull.Value ? false : bool.Parse(dr[recAcceptanceDetail.Fields.IsLeaf].ToString());
                                t.IsExpand = dr[recAcceptanceDetail.Fields.IsExpand] == DBNull.Value ? false : bool.Parse(dr[recAcceptanceDetail.Fields.IsExpand].ToString());

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public bool Update(List<recAcceptanceDetail> domain)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {

                    conn.Open();
                    foreach (recAcceptanceDetail acceps in domain)
                    {
                        SqlCommand cmd = conn.CreateCommand();
                        cmd.CommandText = "UPDATE transactions.recAcceptanceDetail SET [Values] = @Values, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE AcceptanceDetailId = @AcceptanceDetailId and AcceptanceId= @AcceptanceId";
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Add("@AcceptanceDetailId", SqlDbType.Int).Value = acceps.AcceptanceDetailId;
                        cmd.Parameters.Add("@AcceptanceId", SqlDbType.Int).Value = acceps.AcceptanceId;
                        cmd.Parameters.Add("@Values", SqlDbType.Decimal).Value = (object)acceps.Values ?? DBNull.Value;
                        cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)acceps.UpdatedDate ?? DBNull.Value;
                        cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)acceps.UpdatedBy ?? DBNull.Value;


                        result = cmd.ExecuteNonQuery();

                    }


                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }



            return (result > 0);
        }

        public DataTable GetDataReport(string parameters,string parameter2,string paramDueDate,string param3,string param4)
        {
           
            SqlConnection conn = new SqlConnection(connectionString);
            SqlDataAdapter cmd = new SqlDataAdapter(@"SELECT transactions.recAcceptance.AcceptancePeriod 'Year',recSector.SectorName 'Sektor', sum(transactions.recAcceptanceDetail.[Values]) 'Target1'
                                                    FROm
                                                    transactions.recAcceptance   
                                                    INNER JOIN
                                                    transactions.recAcceptanceDetail ON transactions.recAcceptance.AcceptanceId = transactions.recAcceptanceDetail.AcceptanceId   
                                                    INNER JOIN
                                                    master.recSector on master.recSector.sectorcode = transactions.recAcceptanceDetail.SectorCode where transactions.recAcceptance.AcceptancePeriod " + parameters + @"
                                                    and transactions.recAcceptanceDetail.Level = 3 group by transactions.recAcceptance.AcceptancePeriod ,recSector.SectorName order by recSector.SectorName desc ", conn);
            SqlDataAdapter cmd2 = new SqlDataAdapter(@"select sum(jumlah) 'Jumlah',SectorName
 from
 (
 SELECT                                         SUM(recBilling.Value) 'jumlah'
                                                ,recSector.SectorName
                                                FROM      transactions.recBilling
                                                left join transactions.recInvoice on recBilling.InvoiceNo = recInvoice.InvoiceNo                                                
                                                inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID
                                                inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID
                                                inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                inner join master.recSector on recSector.SectorID = recDebtorSector.SectorID
                                                inner join master.recSubSector on recSubSector.SubSectorID = recDebtorSector.SubSectorID
                                                inner join master.recSubSubSector on recSubSubSector.SubSubSectorID = recDebtorSector.SubSubSectorID 
                                                where (recInvoice.PaymentNo IS not NULL or recInvoice.PaymentNo=0) 
                                                AND recTransactionType.TransTypeCode = 2  " +
                                                paramDueDate + parameter2 + @" 
                                                group by recSector.SectorName
                                                UNION     
                                                SELECT   
                                                SUM(recBilling.Value) 'jumlah'
                                                ,recSector.SectorName
                                                FROM transactions.recBilling
                                                left join transactions.recInvoice on recBilling.InvoiceNo = recInvoice.InvoiceNo                                                
                                                inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID
                                                inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID
                                                inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                inner join master.recSector on recSector.SectorID = recDebtorSector.SectorID
                                                inner join master.recSubSector on recSubSector.SubSectorID = recDebtorSector.SubSectorID
                                                inner join master.recSubSubSector on recSubSubSector.SubSubSectorID = recDebtorSector.SubSubSectorID 
                                                where (recInvoice.PaymentNo IS not NULL or recInvoice.PaymentNo=0) AND recTransactionType.TransTypeCode <> 2
                                                "+ parameter2 + @"group by SectorName ) x
                                                group by SectorName order by SectorName desc", conn);
            SqlDataAdapter cmd3 = new SqlDataAdapter(@"SELECT transactions.recAcceptance.AcceptancePeriod 'Year',recSector.SectorName 'SectorName', sum(transactions.recAcceptanceDetail.[Values]) 'Target'
                                                    FROm
                                                    transactions.recAcceptance   
                                                    INNER JOIN
                                                    transactions.recAcceptanceDetail ON transactions.recAcceptance.AcceptanceId = transactions.recAcceptanceDetail.AcceptanceId   
                                                    INNER JOIN
                                                    master.recSector on master.recSector.sectorcode = transactions.recAcceptanceDetail.SectorCode where transactions.recAcceptance.AcceptancePeriod " + param3 + @"
                                                    and transactions.recAcceptanceDetail.Level = 3 group by transactions.recAcceptance.AcceptancePeriod ,recSector.SectorName order by recSector.SectorName desc", conn);
            SqlDataAdapter cmd4 = new SqlDataAdapter(@"select sum(jumlah) 'Jumlah',SectorName
 from
 (
 SELECT                                         SUM(recBilling.Value) 'jumlah'
                                                ,recSector.SectorName
                                                FROM      transactions.recBilling
                                                left join transactions.recInvoice on recBilling.InvoiceNo = recInvoice.InvoiceNo                                                
                                                inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID
                                                inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID
                                                inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                inner join master.recSector on recSector.SectorID = recDebtorSector.SectorID
                                                inner join master.recSubSector on recSubSector.SubSectorID = recDebtorSector.SubSectorID
                                                inner join master.recSubSubSector on recSubSubSector.SubSubSectorID = recDebtorSector.SubSubSectorID 
                                                where (recInvoice.PaymentNo IS not NULL or recInvoice.PaymentNo=0) 
                                                AND recTransactionType.TransTypeCode = 2  " +
                                                paramDueDate + param4 + @" 
                                                group by recSector.SectorName
                                                UNION     
                                                SELECT   
                                                SUM(recBilling.Value) 'jumlah'
                                                ,recSector.SectorName
                                                FROM transactions.recBilling
                                                left join transactions.recInvoice on recBilling.InvoiceNo = recInvoice.InvoiceNo                                                
                                                inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID
                                                inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID
                                                inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                inner join master.recSector on recSector.SectorID = recDebtorSector.SectorID
                                                inner join master.recSubSector on recSubSector.SubSectorID = recDebtorSector.SubSectorID
                                                inner join master.recSubSubSector on recSubSubSector.SubSubSectorID = recDebtorSector.SubSubSectorID 
                                                where (recInvoice.PaymentNo IS not NULL or recInvoice.PaymentNo=0) AND recTransactionType.TransTypeCode <> 2
                                                " + param4 + @"group by recSector.SectorName ) x
                                                group by SectorName order by SectorName desc", conn);
            try
            {
               
                DataTable dt = new DataTable();
                DataTable dt2 = new DataTable();
                DataTable dt3 = new DataTable();
                DataTable dt4 = new DataTable();
                cmd.Fill(dt);
                cmd2.Fill(dt2);
                cmd3.Fill(dt3);
                cmd4.Fill(dt4);
              
                dt.Columns.Add("Realisasi1", typeof(decimal));
                dt.Columns.Add("Target2", typeof(decimal));
                dt.Columns.Add("Realisasi2", typeof(decimal));
               
                for (int i = 0;i< dt.Rows.Count;i++ )
                {
                   
                    try
                    {
                        dt.Rows[i][4] = Convert.ToDecimal(dt3.Rows[i][2]);
                    }
                    catch {
                        dt.Rows[i][4] = Convert.ToDecimal(0);
                    }
                    bool stat1 = false;
                    for (int c = 0; c < dt2.Rows.Count; c++)
                        {
                            if (dt.Rows[i][1].ToString() == dt2.Rows[c][1].ToString())
                            {
                                try
                                {
                                    dt.Rows[i][3] = Convert.ToDecimal(dt2.Rows[c][0]);
                                }
                                catch {
                                    dt.Rows[i][3] = Convert.ToDecimal(0);
                                }
                                stat1 = true;
                                break;
                              
                            }
                            

                        }
                    if (stat1==false)
                    {

                        dt.Rows[i][3] = Convert.ToDecimal(0);
                    }

                    bool stat2 = false;
                    for (int c = 0; c < dt4.Rows.Count; c++)
                    {
                        if (dt.Rows[i][1].ToString() == dt4.Rows[c][1].ToString() )
                        {
                           
                            try
                            {
                                dt.Rows[i][5] = Convert.ToDecimal(dt4.Rows[c][0]);
                            }
                            catch
                            {
                                dt.Rows[i][5] = Convert.ToDecimal(0);
                            }
                            // stat = true;
                            stat2 = true;
                            break;
                   
                        }


                    }
                    if (stat2 == false)
                    {
                        dt.Rows[i][5] = Convert.ToDecimal(0);
                    }
                 

                   
                }
             
                return dt;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                //if (conn.State == ConnectionState.Open) conn.Close();
            }
        }
    }
}
